/*	
	EXEC SP_YIELD_COST_VARIENCE_REPORT '0','0','0','0','06/01/2009','07/20/2009'
*/
ALTER PROCEDURE SP_YIELD_COST_VARIENCE_REPORT
(
	@PROCESS_TYPE		VARCHAR(50),
	@PROCESSSTEP_ID 	VARCHAR(50),
	@BASEPRODUCT_ID		VARCHAR(50),
	@VENDOR			    VARCHAR(50),
	@FROMDATE		    VARCHAR(50),
	@TODATE 		    VARCHAR(50)
)

 AS
 BEGIN
	
	DECLARE @CURRENT_QUARTER INT
	DECLARE @CURRENT_YEAR   INT
	
	DECLARE @PROCESS_STEPS_CURSOR CURSOR
	DECLARE @YIELD FLOAT
	DECLARE @FT_YIELD FLOAT
	DECLARE @QA_YIELD FLOAT
	DECLARE @PROCESS_STEP_ID INT
	DECLARE @LOT_ID INT
	DECLARE @ACT_YIELD FLOAT
	DECLARE @STD_COST NUMERIC(10,4)
	DECLARE @CERAMIC_COST NUMERIC(10,4)
	DECLARE @DIE_COST NUMERIC(10,4)
	DECLARE @QTY_OUT INT
	DECLARE @QTY_IN INT
	DECLARE @DIE_PER_PACKAGE INT
	DECLARE @PS_Type VARCHAR(50)
	DECLARE @CERAMIC_DIE_QTY INT
	DECLARE @CERAMIC_PROCESSSTEP_ID INT

	IF @TODATE IS NULL OR @TODATE = '0'
	BEGIN			
			SET @TODATE = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
	END
	
	SET @CURRENT_YEAR = YEAR(@TODATE)
	SELECT @CURRENT_QUARTER = CQC_QUARTER_NO FROM Company_Quarterly_Calendar WHERE CONVERT( VARCHAR, @TODATE ) >= CQC_START_DATE AND CONVERT( VARCHAR, @TODATE ) <= CQC_END_DATE

	CREATE TABLE #RESULT_TABLE ( [BASE PRODUCT] VARCHAR(50), [PROCESS STEP ID] INT, [LOT ID] INT, [PART NUMBER] VARCHAR(50), [LOT NUMBER] VARCHAR(50),[FAB LOT NUMBER] VARCHAR(50),
						[Vendor]	VARCHAR(50),[PROCESS TYPE] VARCHAR(20),[In Date] DATETIME, [Ship Date] DATETIME, 
						[In Qty] INT, [Actual Out Qty] INT, [Std Yield Out Qty] INT, [Std Loss Qty] INT,[Std Yield %] FLOAT,[Actual Yield %] FLOAT,
						[Std Cost] NUMERIC(10,4),[Std Yielded Cost] NUMERIC(10,4),[OutPut Std Cost] NUMERIC(10,4),[Actual Inventory Cost] NUMERIC(10,4),[Cost Diff] NUMERIC(10,4), [Std Yield Inv Cost] NUMERIC(10,4)
 )
  	
	DECLARE @SQLSTRING	NVARCHAR(4000)                
	DECLARE @SQLSTRING1	NVARCHAR(4000)
	DECLARE @EXCELEXECUTESTRING	NVARCHAR(4000)
			
	SET @SQLSTRING = N' INSERT INTO #result_table
						SELECT (SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
				), LT.PROCESSSTEP_ID, LT.LOT_ID,
				P.PART_NUMBER, LOT_NUMBER,FAB_LOT_NUMBER, L_VENDOR_NAME, PS_TYPE,
				ISNULL((SELECT MIN(DATE_IN) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID ), ISNULL(LT.DATE_IN,LT.TIMESTAMP) ) AS DATE_IN ,
				ISNULL((SELECT MAX(SHIP_DATE) FROM SHIPPED_LOT WHERE LOT_ID = LT.LOT_ID), SL.TIMESTAMP) AS DATE_OUT ,
				ISNULL((SELECT MAX(QTY_IN) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID ), LT.QTY_IN ) AS QTY_IN,
				(SELECT SUM(SHIP_QTY) FROM SHIPPED_LOT WHERE LOT_ID = LT.LOT_ID) AS QTY_OUT, null,
				NULL,
				NULL,
				ROUND(CAST((SELECT SUM(SHIP_QTY) FROM SHIPPED_LOT WHERE LOT_ID = LT.LOT_ID) AS FLOAT) * 100 / CAST(ISNULL((SELECT MAX(QTY_IN) FROM LOT_TABLE_HISTORY WHERE LOT_ID = LT.LOT_ID ), LT.QTY_IN ) AS FLOAT), 2)  AS YIELD,
				NULL, NULL, NULL, NULL, NULL, NULL			
				FROM PRODUCT P, PROCESS_STEP PS, LOCATION L, LOT_TABLE LT, SHIPPED_LOT SL
				WHERE P.PROCESSSTEP_ID=PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID 
				 AND LT.LOCATION_ID = L.LOCATION_ID AND LT.LOT_ID= SL.LOT_ID AND lot_current_status = ''close'' '
	
	SET @SQLSTRING1 = ''
	
	IF ( @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0')
	BEGIN
		 SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 
	END
	ELSE
	BEGIN
			SET @SQLSTRING1 = @SQLSTRING1 + ' AND PS.PS_TYPE IN (''ASSEMBLY'',''FT'' ) '
	END
	IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND LT.PROCESSSTEP_ID = ''' +  @PROCESSSTEP_ID + '''' 
	END 

	IF ( @BASEPRODUCT_ID IS NOT NULL AND @BASEPRODUCT_ID <> '0' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND P_PARENT_PRODUCT_ID =  ''' +  @BASEPRODUCT_ID + '''' 
	END
	
	IF ( @VENDOR IS NOT NULL AND @VENDOR <> '0' AND @VENDOR <> 'All Vendors')
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' and L_VENDOR_NAME =  ''' + @VENDOR + '''' 
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SHIP_DATE >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
	END
	
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @SQLSTRING1 = @SQLSTRING1 + ' AND SHIP_DATE <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
	END

	--PRINT @SQLSTRING + @SQLSTRING1

	EXEC (@SQLSTRING + @SQLSTRING1)
	SET @CERAMIC_COST = 0.0

	SET @PROCESS_STEPS_CURSOR = CURSOR FOR SELECT [PROCESS STEP ID],[LOT ID], [In Qty],[Actual Out Qty], [Actual Yield %] FROM #result_table
	OPEN @PROCESS_STEPS_CURSOR
	FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESS_STEP_ID,@LOT_ID,@QTY_IN, @QTY_OUT, @ACT_YIELD
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--SELECT * FROM Update_View_Cost_Table
		SELECT @STD_COST = CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_STANDARD_COST
				 	WHEN @CURRENT_QUARTER = 2 THEN Q2_STANDARD_COST
					WHEN @CURRENT_QUARTER = 3 THEN Q3_STANDARD_COST
					WHEN @CURRENT_QUARTER = 4 THEN Q4_STANDARD_COST END FROM Update_View_Cost_Table WHERE ProcessStep_id = @PROCESS_STEP_ID AND YEAR(Fin_Year) = @CURRENT_YEAR

		SELECT @YIELD = ISNULL( Parameter_Value, 100 ) FROM Process_Parameter WHERE Parameter_Key = 'Target Yield' AND ProcessStep_Id = @PROCESS_STEP_ID
		SELECT @PS_Type = PS_Type FROM Process_Step WHERE ProcessStep_ID = @PROCESS_STEP_ID
		IF( @PS_Type = 'Assembly' )
		BEGIN
			SELECT @FT_YIELD = ISNULL( Parameter_Value, 100 ) FROM Process_Parameter WHERE Parameter_Key = 'Target Yield' AND ProcessStep_Id = (SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESS_STEP_ID, 1 ) WHERE  PS_Type = 'FT')
			SELECT @QA_YIELD = ISNULL( Parameter_Value, 100 ) FROM Process_Parameter WHERE Parameter_Key = 'Target Yield' AND ProcessStep_Id = (SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESS_STEP_ID, 1 ) WHERE  PS_Type = 'QA')

			SELECT @CERAMIC_DIE_QTY = Qty, @CERAMIC_PROCESSSTEP_ID = bom_processStep_ID FROM assembly_bom_setup_table WHERE Assembly_ProcessStep_Id = @PROCESS_STEP_ID
			SET @CERAMIC_COST = 0.093 * @CERAMIC_DIE_QTY
			
			SELECT @DIE_COST = CASE WHEN @CURRENT_QUARTER = 1 THEN Q1_STANDARD_COST
				 	WHEN @CURRENT_QUARTER = 2 THEN Q2_STANDARD_COST
					WHEN @CURRENT_QUARTER = 3 THEN Q3_STANDARD_COST
					WHEN @CURRENT_QUARTER = 4 THEN Q4_STANDARD_COST END FROM Update_View_Cost_Table WHERE ProcessStep_id = (SELECT TOP 1 ProcessStep_ID FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID( @PROCESS_STEP_ID, 0 ) WHERE  PS_Type = 'Die Bank') AND YEAR(Fin_Year) = @CURRENT_YEAR

			SELECT @DIE_PER_PACKAGE = ISNULL( Parameter_Value, 1 ) FROM Process_Parameter WHERE Parameter_Key = 'Dies/Package' AND ProcessStep_Id = @PROCESS_STEP_ID
			
			
			IF @FT_YIELD IS NULL or @FT_YIELD = 0
			BEGIN
				SET @FT_YIELD = 100
			END
			IF @QA_YIELD IS NULL or @QA_YIELD = 0
			BEGIN
				SET @QA_YIELD = 100
			END
			IF @YIELD IS NULL or @YIELD = 0
			BEGIN
				SET @YIELD = 100
			END
		
			SET @YIELD = (@YIELD*@QA_YIELD*@FT_YIELD)/(100*100)	
		END

		IF @YIELD IS NULL or @YIELD = 0
		BEGIN
			SET @YIELD = 100
		END
		
		UPDATE #result_table SET [Std Yield %]=@YIELD 
								,[Std Yielded Cost] = @STD_COST ,[OutPut Std Cost] = ((@STD_COST * 100)/@YIELD),[Std Yield Out Qty] = [In Qty] * @YIELD/100,[Std Loss Qty] = ([In Qty] * @YIELD/100)-@QTY_OUT,
		[Actual Inventory Cost] = ( @QTY_OUT * @STD_COST ) ,[Cost Diff] = ( ((ISNULL(@YIELD,100)-@ACT_YIELD )/100) * @STD_COST * @QTY_IN ),
		[Std Cost] = @DIE_PER_PACKAGE * @DIE_COST + ISNULL( @CERAMIC_COST, 0.0), [Std Yield Inv Cost] = @STD_COST * [In Qty] * (@YIELD/100)
		WHERE [PROCESS STEP ID] = @PROCESS_STEP_ID AND [LOT ID] = @LOT_ID

		SET @PROCESS_STEP_ID = NULL
		SET @YIELD = null
		SET @LOT_ID = NULL
		SET @QTY_IN = NULL
		SET @QTY_OUT = NULL
		SET @ACT_YIELD = NULL
		FETCH NEXT FROM @PROCESS_STEPS_CURSOR INTO @PROCESS_STEP_ID,@LOT_ID,@QTY_IN,@QTY_OUT, @ACT_YIELD
	 END
	CLOSE @PROCESS_STEPS_CURSOR
	DEALLOCATE @PROCESS_STEPS_CURSOR

	--SELECT * FROM #result_table

	SET @EXCELEXECUTESTRING = 'SELECT [BASE PRODUCT] ,[PART NUMBER] , [LOT NUMBER],[FAB LOT NUMBER],[Vendor],[PROCESS TYPE] ,[In Date], [Ship Date], 
						[In Qty], [Std Yield Out Qty], [Actual Out Qty],[Std Loss Qty],[Std Yield %], [Actual Yield %],
						[Std Cost],[Std Yielded Cost],[Std Yield Inv Cost],[Actual Inventory Cost],[Cost Diff]
  	 FROM #result_table WHERE 1=1'

	/*IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' AND LTRIM(RTRIM(@FROMDATE)) <> '' )
	BEGIN
		SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Ship Date] >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00 AM' +''''
	END

	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' AND LTRIM(RTRIM(@TODATE)) <> '' )
	BEGIN
		SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' AND [Ship Date] <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59 PM' +''''
	END*/

	SET @EXCELEXECUTESTRING = @EXCELEXECUTESTRING + ' order by [BASE PRODUCT] ,[PART NUMBER],[In Date] desc '
	
	EXEC (@EXCELEXECUTESTRING) 
END